/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package utilities;

import java.sql.*;

/**
 *
 * @author baprung
 */
public class Conn {

    private String servername = null;
    private String port = null;
    private String database = null;
    private String username = null;
    private String password = null;
    public Connection conn = null;
    /**
     * create default connection object
     */
    public Conn() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = (Connection) DriverManager.getConnection(Constants.URL,Constants.USER_NAME,Constants.PASS_WORD);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    /**
     * Create connection object with parameter from user
     * @param ServerName 
     * @param Port
     * @param User
     * @param Pass
     * @param Database 
     */
    public Conn(String ServerName, String Port, String User,
            String Pass, String Database) {
        servername = ServerName;
        port = Port;
        database = Database;
        username = User;
        password = Pass;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = (Connection) DriverManager.getConnection(getConnectionStringMySql());
            //Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            //conn = (Connection) DriverManager.getConnection(getConnectionStringMSSql());
        } catch (Exception ex) {
            ex.printStackTrace();

        }
    }
    /**
     * 
     * @return get full connection string(Mysql) 
     * @throws Exception 
     */
    private String getConnectionStringMySql() throws Exception {
        StringBuilder sb = new StringBuilder();
        sb.append("jdbc:mysql://");
        sb.append(servername);
        sb.append(":");
        sb.append(port);
        sb.append("/");
        sb.append(database);
        sb.append("?user=");
        sb.append(username);
        sb.append("&password=");
        sb.append(password);
        return sb.toString();
    }
    /**
     * get full connection string (MSSQL)
     * @return
     * @throws Exception 
     */
    private String getConnectionStringMSSql() throws Exception {
        StringBuilder sb = new StringBuilder();
        sb.append("jdbc:sqlserver://");
        sb.append(servername);
        sb.append(":");
        sb.append(port);
        sb.append(";databaseName=");
        sb.append(database);
        sb.append(";user=");
        sb.append(username);
        sb.append(";password=");
        sb.append(password);
        return sb.toString();
    }
    /**
     * execute select query
     * @param sql
     * @return
     * @throws SQLException 
     */
    public ResultSet exec(String sql) throws SQLException {
        Statement query = conn.createStatement();
        ResultSet rs = query.executeQuery(sql);
        return rs;
    }
    /**
     * execute update query
     * @param sql
     * @return
     * @throws SQLException 
     */
    public int upd(String sql) throws SQLException {
        Statement query = conn.createStatement();
        int rss = query.executeUpdate(sql);
        return rss;
    }
    /**
     * execute insert query
     * @param sql
     * @return
     * @throws SQLException 
     */
    public int ins(String sql) throws SQLException {
        Statement query = conn.createStatement();
        int rs1 = query.executeUpdate(sql);
        return rs1;

    }
    /**
     * execute delete query
     * @param sql
     * @return
     * @throws SQLException 
     */
    public int delete(String sql) throws SQLException {
        Statement query = conn.createStatement();
        int rs1 = query.executeUpdate(sql);
        return rs1;
    }
}
